﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Maticsoft.DBUtility;
using System.Collections.Generic;//Please add references
namespace Hermes.DAL
{
    /// <summary>
    /// 数据访问类:Orders
    /// </summary>
    public partial class Orders
    {
        public Orders()
        { }
        #region  Method



        /// <summary>
        /// 增加一条数据
        /// </summary>
        public long Add(Hermes.Model.Orders model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into Orders(");
            strSql.Append("OrderNumber,Status,OrderTime,Amount,SiteID,AddTime,Contact,Company,Street,City,Province,Country,CountryCode,Postcode,Phone,Email,ModifyOn,ShippingMethod,StoreID,ImportOn,ImportBy,PickUpAddressID,ShipFromAddressID,Notes)");
            strSql.Append(" values (");
            strSql.Append("@OrderNumber,@Status,@OrderTime,@Amount,@SiteID,@AddTime,@Contact,@Company,@Street,@City,@Province,@Country,@CountryCode,@Postcode,@Phone,@Email,@ModifyOn,@ShippingMethod,@StoreID,@ImportOn,@ImportBy,@PickUpAddressID,@ShipFromAddressID,@Notes)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@OrderNumber", SqlDbType.NVarChar,200),
					new SqlParameter("@Status", SqlDbType.Int,4),
					new SqlParameter("@OrderTime", SqlDbType.DateTime),
					new SqlParameter("@Amount", SqlDbType.Float,8),
					new SqlParameter("@SiteID", SqlDbType.Int,4),
					new SqlParameter("@AddTime", SqlDbType.DateTime),
					new SqlParameter("@Contact", SqlDbType.NVarChar,200),
					new SqlParameter("@Company", SqlDbType.NVarChar,200),
					new SqlParameter("@Street", SqlDbType.NVarChar,200),
					new SqlParameter("@City", SqlDbType.NVarChar,200),
					new SqlParameter("@Province", SqlDbType.NVarChar,200),
					new SqlParameter("@Country", SqlDbType.NVarChar,200),
					new SqlParameter("@CountryCode", SqlDbType.NVarChar,200),
					new SqlParameter("@Postcode", SqlDbType.NVarChar,200),
					new SqlParameter("@Phone", SqlDbType.NVarChar,200),
					new SqlParameter("@Email", SqlDbType.NVarChar,200),
					new SqlParameter("@ModifyOn", SqlDbType.DateTime),
					new SqlParameter("@ShippingMethod", SqlDbType.Int,4),
					new SqlParameter("@StoreID", SqlDbType.Int,4),
					new SqlParameter("@ImportOn", SqlDbType.DateTime),
					new SqlParameter("@ImportBy", SqlDbType.NVarChar,50),
					new SqlParameter("@PickUpAddressID", SqlDbType.Int,4),
					new SqlParameter("@ShipFromAddressID", SqlDbType.Int,4),
                    new SqlParameter("@Notes", SqlDbType.NVarChar)};
            parameters[0].Value = model.OrderNumber;
            parameters[1].Value = model.Status;
            parameters[2].Value = model.OrderTime;
            parameters[3].Value = model.Amount;
            parameters[4].Value = model.SiteID;
            parameters[5].Value = DateTime.UtcNow;
            parameters[6].Value = model.Contact;
            parameters[7].Value = model.Company;
            parameters[8].Value = model.Street;
            parameters[9].Value = model.City;
            parameters[10].Value = model.Province;
            parameters[11].Value = model.Country;
            parameters[12].Value = model.CountryCode;
            parameters[13].Value = model.Postcode;
            parameters[14].Value = model.Phone;
            parameters[15].Value = model.Email;
            parameters[16].Value = DateTime.UtcNow;
            parameters[17].Value = model.ShippingMethod;
            parameters[18].Value = model.StoreID;
            parameters[19].Value = DateTime.UtcNow;
            parameters[20].Value = model.ImportBy;
            parameters[21].Value = 1;
            parameters[22].Value = 2;
            parameters[23].Value = model.Notes;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt64(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Hermes.Model.Orders model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Orders set ");
            strSql.Append("OrderNumber=@OrderNumber,");
            strSql.Append("Status=@Status,");
            strSql.Append("OrderTime=@OrderTime,");
            strSql.Append("Amount=@Amount,");
            strSql.Append("SiteID=@SiteID,");
            strSql.Append("AddTime=@AddTime,");
            strSql.Append("Contact=@Contact,");
            strSql.Append("Company=@Company,");
            strSql.Append("Street=@Street,");
            strSql.Append("City=@City,");
            strSql.Append("Province=@Province,");
            strSql.Append("Country=@Country,");
            strSql.Append("CountryCode=@CountryCode,");
            strSql.Append("Postcode=@Postcode,");
            strSql.Append("Phone=@Phone,");
            strSql.Append("Email=@Email,");
            strSql.Append("TrackCode=@TrackCode,");
            strSql.Append("ModifyOn=@ModifyOn,");
            strSql.Append("ShippingMethod=@ShippingMethod,");
            strSql.Append("StoreID=@StoreID,");
            strSql.Append("ShippingOn=@ShippingOn,");
            strSql.Append("ShippingBy=@ShippingBy,");
            strSql.Append("ImportOn=@ImportOn,");
            strSql.Append("ImportBy=@ImportBy,");
            strSql.Append("CustomerID=@CustomerID,");
            strSql.Append("PickUpAddressID=@PickUpAddressID,");
            strSql.Append("ShipFromAddressID=@ShipFromAddressID,");
            strSql.Append("Notes=@Notes");
            strSql.Append(" where ID=@ID");
            SqlParameter[] parameters = {
					new SqlParameter("@OrderNumber", SqlDbType.NVarChar,200),
					new SqlParameter("@Status", SqlDbType.Int,4),
					new SqlParameter("@OrderTime", SqlDbType.DateTime),
					new SqlParameter("@Amount", SqlDbType.Float,8),
					new SqlParameter("@SiteID", SqlDbType.Int,4),
					new SqlParameter("@AddTime", SqlDbType.DateTime),
					new SqlParameter("@Contact", SqlDbType.NVarChar,200),
					new SqlParameter("@Company", SqlDbType.NVarChar,200),
					new SqlParameter("@Street", SqlDbType.NVarChar,200),
					new SqlParameter("@City", SqlDbType.NVarChar,200),
					new SqlParameter("@Province", SqlDbType.NVarChar,200),
					new SqlParameter("@Country", SqlDbType.NVarChar,200),
					new SqlParameter("@CountryCode", SqlDbType.NVarChar,200),
					new SqlParameter("@Postcode", SqlDbType.NVarChar,200),
					new SqlParameter("@Phone", SqlDbType.NVarChar,200),
					new SqlParameter("@Email", SqlDbType.NVarChar,200),
					new SqlParameter("@TrackCode", SqlDbType.NVarChar,200),
					new SqlParameter("@ModifyOn", SqlDbType.DateTime),
					new SqlParameter("@ShippingMethod", SqlDbType.Int,4),
					new SqlParameter("@StoreID", SqlDbType.Int,4),
					new SqlParameter("@ShippingOn", SqlDbType.DateTime),
					new SqlParameter("@ShippingBy", SqlDbType.NVarChar,50),
					new SqlParameter("@ImportOn", SqlDbType.DateTime),
					new SqlParameter("@ImportBy", SqlDbType.NVarChar,50),
					new SqlParameter("@CustomerID", SqlDbType.Int,4),
					new SqlParameter("@PickUpAddressID", SqlDbType.Int,4),
					new SqlParameter("@ShipFromAddressID", SqlDbType.Int,4),
                    new SqlParameter("@Notes", SqlDbType.NVarChar),
					new SqlParameter("@ID", SqlDbType.BigInt,8)};
            parameters[0].Value = model.OrderNumber;
            parameters[1].Value = model.Status;
            parameters[2].Value = model.OrderTime;
            parameters[3].Value = model.Amount;
            parameters[4].Value = model.SiteID;
            parameters[5].Value = model.AddTime;
            parameters[6].Value = model.Contact;
            parameters[7].Value = model.Company;
            parameters[8].Value = model.Street;
            parameters[9].Value = model.City;
            parameters[10].Value = model.Province;
            parameters[11].Value = model.Country;
            parameters[12].Value = model.CountryCode;
            parameters[13].Value = model.Postcode;
            parameters[14].Value = model.Phone;
            parameters[15].Value = model.Email;
            parameters[16].Value = model.TrackCode;
            parameters[17].Value = model.ModifyOn;
            parameters[18].Value = model.ShippingMethod;
            parameters[19].Value = model.StoreID;
            parameters[20].Value = model.ShippingOn;
            parameters[21].Value = model.ShippingBy;
            parameters[22].Value = model.ImportOn;
            parameters[23].Value = model.ImportBy;
            parameters[24].Value = model.CustomerID;
            parameters[25].Value = model.PickUpAddressID;
            parameters[26].Value = model.ShipFromAddressID;
            parameters[27].Value = model.Notes;
            parameters[28].Value = model.ID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(long ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Orders ");
            strSql.Append(" where ID=@ID");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.BigInt)
};
            parameters[0].Value = ID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(string orderNumber, int siteID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Orders ");
            strSql.Append(" where OrderNumber=@OrderNumber and SiteID = @SiteID");
            SqlParameter[] parameters = {
					new SqlParameter("@OrderNumber", SqlDbType.NVarChar,200),
                    new SqlParameter("@SiteID", SqlDbType.Int)
};
            parameters[0].Value = orderNumber;
            parameters[1].Value = siteID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string IDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Orders ");
            strSql.Append(" where ID in (" + IDlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Hermes.Model.Orders GetModel(long ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 ID,OrderNumber,Status,OrderTime,Amount,SiteID,AddTime,Contact,Company,Street,City,Province,Country,CountryCode,Postcode,Phone,Email,TrackCode,ModifyOn,ShippingMethod,StoreID,ShippingOn,ShippingBy,ImportOn,ImportBy,LastUpdate,CustomerID,PickUpAddressID,ShipFromAddressID from Orders ");
            strSql.Append(" where ID=@ID");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.BigInt)
};
            parameters[0].Value = ID;

            Hermes.Model.Orders model = new Hermes.Model.Orders();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"] != null && ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    model.ID = long.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["OrderNumber"] != null && ds.Tables[0].Rows[0]["OrderNumber"].ToString() != "")
                {
                    model.OrderNumber = ds.Tables[0].Rows[0]["OrderNumber"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Status"] != null && ds.Tables[0].Rows[0]["Status"].ToString() != "")
                {
                    model.Status = int.Parse(ds.Tables[0].Rows[0]["Status"].ToString());
                }
                if (ds.Tables[0].Rows[0]["OrderTime"] != null && ds.Tables[0].Rows[0]["OrderTime"].ToString() != "")
                {
                    model.OrderTime = DateTime.Parse(ds.Tables[0].Rows[0]["OrderTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Amount"] != null && ds.Tables[0].Rows[0]["Amount"].ToString() != "")
                {
                    model.Amount = decimal.Parse(ds.Tables[0].Rows[0]["Amount"].ToString());
                }
                if (ds.Tables[0].Rows[0]["SiteID"] != null && ds.Tables[0].Rows[0]["SiteID"].ToString() != "")
                {
                    model.SiteID = int.Parse(ds.Tables[0].Rows[0]["SiteID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["AddTime"] != null && ds.Tables[0].Rows[0]["AddTime"].ToString() != "")
                {
                    model.AddTime = DateTime.Parse(ds.Tables[0].Rows[0]["AddTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Contact"] != null && ds.Tables[0].Rows[0]["Contact"].ToString() != "")
                {
                    model.Contact = ds.Tables[0].Rows[0]["Contact"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Company"] != null && ds.Tables[0].Rows[0]["Company"].ToString() != "")
                {
                    model.Company = ds.Tables[0].Rows[0]["Company"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Street"] != null && ds.Tables[0].Rows[0]["Street"].ToString() != "")
                {
                    model.Street = ds.Tables[0].Rows[0]["Street"].ToString();
                }
                if (ds.Tables[0].Rows[0]["City"] != null && ds.Tables[0].Rows[0]["City"].ToString() != "")
                {
                    model.City = ds.Tables[0].Rows[0]["City"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Province"] != null && ds.Tables[0].Rows[0]["Province"].ToString() != "")
                {
                    model.Province = ds.Tables[0].Rows[0]["Province"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Country"] != null && ds.Tables[0].Rows[0]["Country"].ToString() != "")
                {
                    model.Country = ds.Tables[0].Rows[0]["Country"].ToString();
                }
                if (ds.Tables[0].Rows[0]["CountryCode"] != null && ds.Tables[0].Rows[0]["CountryCode"].ToString() != "")
                {
                    model.CountryCode = ds.Tables[0].Rows[0]["CountryCode"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Postcode"] != null && ds.Tables[0].Rows[0]["Postcode"].ToString() != "")
                {
                    model.Postcode = ds.Tables[0].Rows[0]["Postcode"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Phone"] != null && ds.Tables[0].Rows[0]["Phone"].ToString() != "")
                {
                    model.Phone = ds.Tables[0].Rows[0]["Phone"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Email"] != null && ds.Tables[0].Rows[0]["Email"].ToString() != "")
                {
                    model.Email = ds.Tables[0].Rows[0]["Email"].ToString();
                }
                if (ds.Tables[0].Rows[0]["TrackCode"] != null && ds.Tables[0].Rows[0]["TrackCode"].ToString() != "")
                {
                    model.TrackCode = ds.Tables[0].Rows[0]["TrackCode"].ToString();
                }
                if (ds.Tables[0].Rows[0]["ModifyOn"] != null && ds.Tables[0].Rows[0]["ModifyOn"].ToString() != "")
                {
                    model.ModifyOn = DateTime.Parse(ds.Tables[0].Rows[0]["ModifyOn"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ShippingMethod"] != null && ds.Tables[0].Rows[0]["ShippingMethod"].ToString() != "")
                {
                    model.ShippingMethod = int.Parse(ds.Tables[0].Rows[0]["ShippingMethod"].ToString());
                }
                if (ds.Tables[0].Rows[0]["StoreID"] != null && ds.Tables[0].Rows[0]["StoreID"].ToString() != "")
                {
                    model.StoreID = int.Parse(ds.Tables[0].Rows[0]["StoreID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ShippingOn"] != null && ds.Tables[0].Rows[0]["ShippingOn"].ToString() != "")
                {
                    model.ShippingOn = DateTime.Parse(ds.Tables[0].Rows[0]["ShippingOn"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ShippingBy"] != null && ds.Tables[0].Rows[0]["ShippingBy"].ToString() != "")
                {
                    model.ShippingBy = ds.Tables[0].Rows[0]["ShippingBy"].ToString();
                }
                if (ds.Tables[0].Rows[0]["ImportOn"] != null && ds.Tables[0].Rows[0]["ImportOn"].ToString() != "")
                {
                    model.ImportOn = DateTime.Parse(ds.Tables[0].Rows[0]["ImportOn"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ImportBy"] != null && ds.Tables[0].Rows[0]["ImportBy"].ToString() != "")
                {
                    model.ImportBy = ds.Tables[0].Rows[0]["ImportBy"].ToString();
                }
                //if (ds.Tables[0].Rows[0]["LastUpdate"] != null && ds.Tables[0].Rows[0]["LastUpdate"].ToString() != "")
                //{
                //    model.LastUpdate = DateTime.Parse(ds.Tables[0].Rows[0]["LastUpdate"].ToString());
                //}
                if (ds.Tables[0].Rows[0]["CustomerID"] != null && ds.Tables[0].Rows[0]["CustomerID"].ToString() != "")
                {
                    model.CustomerID = int.Parse(ds.Tables[0].Rows[0]["CustomerID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["PickUpAddressID"] != null && ds.Tables[0].Rows[0]["PickUpAddressID"].ToString() != "")
                {
                    model.PickUpAddressID = int.Parse(ds.Tables[0].Rows[0]["PickUpAddressID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ShipFromAddressID"] != null && ds.Tables[0].Rows[0]["ShipFromAddressID"].ToString() != "")
                {
                    model.ShipFromAddressID = int.Parse(ds.Tables[0].Rows[0]["ShipFromAddressID"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Hermes.Model.Orders GetModel(string trackingNo)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 ID,OrderNumber,Status,OrderTime,Amount,SiteID,AddTime,Contact,Company,Street,City,Province,Country,CountryCode,Postcode,Phone,Email,TrackCode,ModifyOn,ShippingMethod,StoreID,ShippingOn,ShippingBy,ImportOn,ImportBy,LastUpdate,CustomerID,PickUpAddressID,ShipFromAddressID from Orders ");
            strSql.Append(" where  TrackCode=@TrackCode");
            SqlParameter[] parameters = {
                    new SqlParameter("@TrackCode", SqlDbType.NVarChar,200)
};
            parameters[0].Value = trackingNo;

            Hermes.Model.Orders model = new Hermes.Model.Orders();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"] != null && ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    model.ID = long.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["OrderNumber"] != null && ds.Tables[0].Rows[0]["OrderNumber"].ToString() != "")
                {
                    model.OrderNumber = ds.Tables[0].Rows[0]["OrderNumber"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Status"] != null && ds.Tables[0].Rows[0]["Status"].ToString() != "")
                {
                    model.Status = int.Parse(ds.Tables[0].Rows[0]["Status"].ToString());
                }
                if (ds.Tables[0].Rows[0]["OrderTime"] != null && ds.Tables[0].Rows[0]["OrderTime"].ToString() != "")
                {
                    model.OrderTime = DateTime.Parse(ds.Tables[0].Rows[0]["OrderTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Amount"] != null && ds.Tables[0].Rows[0]["Amount"].ToString() != "")
                {
                    model.Amount = decimal.Parse(ds.Tables[0].Rows[0]["Amount"].ToString());
                }
                if (ds.Tables[0].Rows[0]["SiteID"] != null && ds.Tables[0].Rows[0]["SiteID"].ToString() != "")
                {
                    model.SiteID = int.Parse(ds.Tables[0].Rows[0]["SiteID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["AddTime"] != null && ds.Tables[0].Rows[0]["AddTime"].ToString() != "")
                {
                    model.AddTime = DateTime.Parse(ds.Tables[0].Rows[0]["AddTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Contact"] != null && ds.Tables[0].Rows[0]["Contact"].ToString() != "")
                {
                    model.Contact = ds.Tables[0].Rows[0]["Contact"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Company"] != null && ds.Tables[0].Rows[0]["Company"].ToString() != "")
                {
                    model.Company = ds.Tables[0].Rows[0]["Company"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Street"] != null && ds.Tables[0].Rows[0]["Street"].ToString() != "")
                {
                    model.Street = ds.Tables[0].Rows[0]["Street"].ToString();
                }
                if (ds.Tables[0].Rows[0]["City"] != null && ds.Tables[0].Rows[0]["City"].ToString() != "")
                {
                    model.City = ds.Tables[0].Rows[0]["City"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Province"] != null && ds.Tables[0].Rows[0]["Province"].ToString() != "")
                {
                    model.Province = ds.Tables[0].Rows[0]["Province"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Country"] != null && ds.Tables[0].Rows[0]["Country"].ToString() != "")
                {
                    model.Country = ds.Tables[0].Rows[0]["Country"].ToString();
                }
                if (ds.Tables[0].Rows[0]["CountryCode"] != null && ds.Tables[0].Rows[0]["CountryCode"].ToString() != "")
                {
                    model.CountryCode = ds.Tables[0].Rows[0]["CountryCode"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Postcode"] != null && ds.Tables[0].Rows[0]["Postcode"].ToString() != "")
                {
                    model.Postcode = ds.Tables[0].Rows[0]["Postcode"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Phone"] != null && ds.Tables[0].Rows[0]["Phone"].ToString() != "")
                {
                    model.Phone = ds.Tables[0].Rows[0]["Phone"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Email"] != null && ds.Tables[0].Rows[0]["Email"].ToString() != "")
                {
                    model.Email = ds.Tables[0].Rows[0]["Email"].ToString();
                }
                if (ds.Tables[0].Rows[0]["TrackCode"] != null && ds.Tables[0].Rows[0]["TrackCode"].ToString() != "")
                {
                    model.TrackCode = ds.Tables[0].Rows[0]["TrackCode"].ToString();
                }
                if (ds.Tables[0].Rows[0]["ModifyOn"] != null && ds.Tables[0].Rows[0]["ModifyOn"].ToString() != "")
                {
                    model.ModifyOn = DateTime.Parse(ds.Tables[0].Rows[0]["ModifyOn"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ShippingMethod"] != null && ds.Tables[0].Rows[0]["ShippingMethod"].ToString() != "")
                {
                    model.ShippingMethod = int.Parse(ds.Tables[0].Rows[0]["ShippingMethod"].ToString());
                }
                if (ds.Tables[0].Rows[0]["StoreID"] != null && ds.Tables[0].Rows[0]["StoreID"].ToString() != "")
                {
                    model.StoreID = int.Parse(ds.Tables[0].Rows[0]["StoreID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ShippingOn"] != null && ds.Tables[0].Rows[0]["ShippingOn"].ToString() != "")
                {
                    model.ShippingOn = DateTime.Parse(ds.Tables[0].Rows[0]["ShippingOn"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ShippingBy"] != null && ds.Tables[0].Rows[0]["ShippingBy"].ToString() != "")
                {
                    model.ShippingBy = ds.Tables[0].Rows[0]["ShippingBy"].ToString();
                }
                if (ds.Tables[0].Rows[0]["ImportOn"] != null && ds.Tables[0].Rows[0]["ImportOn"].ToString() != "")
                {
                    model.ImportOn = DateTime.Parse(ds.Tables[0].Rows[0]["ImportOn"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ImportBy"] != null && ds.Tables[0].Rows[0]["ImportBy"].ToString() != "")
                {
                    model.ImportBy = ds.Tables[0].Rows[0]["ImportBy"].ToString();
                }
                //if (ds.Tables[0].Rows[0]["LastUpdate"] != null && ds.Tables[0].Rows[0]["LastUpdate"].ToString() != "")
                {
                    //model.LastUpdate = DateTime.Parse(ds.Tables[0].Rows[0]["LastUpdate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["CustomerID"] != null && ds.Tables[0].Rows[0]["CustomerID"].ToString() != "")
                {
                    model.CustomerID = int.Parse(ds.Tables[0].Rows[0]["CustomerID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["PickUpAddressID"] != null && ds.Tables[0].Rows[0]["PickUpAddressID"].ToString() != "")
                {
                    model.PickUpAddressID = int.Parse(ds.Tables[0].Rows[0]["PickUpAddressID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ShipFromAddressID"] != null && ds.Tables[0].Rows[0]["ShipFromAddressID"].ToString() != "")
                {
                    model.ShipFromAddressID = int.Parse(ds.Tables[0].Rows[0]["ShipFromAddressID"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Hermes.Model.Orders GetModel(string ordernumber, int siteID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 ID,OrderNumber,Status,OrderTime,Amount,SiteID,AddTime,Contact,Company,Street,City,Province,Country,CountryCode,Postcode,Phone,Email,TrackCode,ModifyOn,ShippingMethod,StoreID,ShippingOn,ShippingBy,ImportOn,ImportBy,LastUpdate,CustomerID,PickUpAddressID,ShipFromAddressID from Orders ");
            strSql.Append(" where siteID=@SiteID and OrderNumber=@OrderNumber");
            SqlParameter[] parameters = {
					new SqlParameter("@siteID", SqlDbType.Int),
                    new SqlParameter("@OrderNumber", SqlDbType.NVarChar,200)
};
            parameters[0].Value = siteID;
            parameters[1].Value = ordernumber;

            Hermes.Model.Orders model = new Hermes.Model.Orders();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"] != null && ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    model.ID = long.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["OrderNumber"] != null && ds.Tables[0].Rows[0]["OrderNumber"].ToString() != "")
                {
                    model.OrderNumber = ds.Tables[0].Rows[0]["OrderNumber"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Status"] != null && ds.Tables[0].Rows[0]["Status"].ToString() != "")
                {
                    model.Status = int.Parse(ds.Tables[0].Rows[0]["Status"].ToString());
                }
                if (ds.Tables[0].Rows[0]["OrderTime"] != null && ds.Tables[0].Rows[0]["OrderTime"].ToString() != "")
                {
                    model.OrderTime = DateTime.Parse(ds.Tables[0].Rows[0]["OrderTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Amount"] != null && ds.Tables[0].Rows[0]["Amount"].ToString() != "")
                {
                    model.Amount = decimal.Parse(ds.Tables[0].Rows[0]["Amount"].ToString());
                }
                if (ds.Tables[0].Rows[0]["SiteID"] != null && ds.Tables[0].Rows[0]["SiteID"].ToString() != "")
                {
                    model.SiteID = int.Parse(ds.Tables[0].Rows[0]["SiteID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["AddTime"] != null && ds.Tables[0].Rows[0]["AddTime"].ToString() != "")
                {
                    model.AddTime = DateTime.Parse(ds.Tables[0].Rows[0]["AddTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Contact"] != null && ds.Tables[0].Rows[0]["Contact"].ToString() != "")
                {
                    model.Contact = ds.Tables[0].Rows[0]["Contact"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Company"] != null && ds.Tables[0].Rows[0]["Company"].ToString() != "")
                {
                    model.Company = ds.Tables[0].Rows[0]["Company"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Street"] != null && ds.Tables[0].Rows[0]["Street"].ToString() != "")
                {
                    model.Street = ds.Tables[0].Rows[0]["Street"].ToString();
                }
                if (ds.Tables[0].Rows[0]["City"] != null && ds.Tables[0].Rows[0]["City"].ToString() != "")
                {
                    model.City = ds.Tables[0].Rows[0]["City"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Province"] != null && ds.Tables[0].Rows[0]["Province"].ToString() != "")
                {
                    model.Province = ds.Tables[0].Rows[0]["Province"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Country"] != null && ds.Tables[0].Rows[0]["Country"].ToString() != "")
                {
                    model.Country = ds.Tables[0].Rows[0]["Country"].ToString();
                }
                if (ds.Tables[0].Rows[0]["CountryCode"] != null && ds.Tables[0].Rows[0]["CountryCode"].ToString() != "")
                {
                    model.CountryCode = ds.Tables[0].Rows[0]["CountryCode"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Postcode"] != null && ds.Tables[0].Rows[0]["Postcode"].ToString() != "")
                {
                    model.Postcode = ds.Tables[0].Rows[0]["Postcode"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Phone"] != null && ds.Tables[0].Rows[0]["Phone"].ToString() != "")
                {
                    model.Phone = ds.Tables[0].Rows[0]["Phone"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Email"] != null && ds.Tables[0].Rows[0]["Email"].ToString() != "")
                {
                    model.Email = ds.Tables[0].Rows[0]["Email"].ToString();
                }
                if (ds.Tables[0].Rows[0]["TrackCode"] != null && ds.Tables[0].Rows[0]["TrackCode"].ToString() != "")
                {
                    model.TrackCode = ds.Tables[0].Rows[0]["TrackCode"].ToString();
                }
                if (ds.Tables[0].Rows[0]["ModifyOn"] != null && ds.Tables[0].Rows[0]["ModifyOn"].ToString() != "")
                {
                    model.ModifyOn = DateTime.Parse(ds.Tables[0].Rows[0]["ModifyOn"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ShippingMethod"] != null && ds.Tables[0].Rows[0]["ShippingMethod"].ToString() != "")
                {
                    model.ShippingMethod = int.Parse(ds.Tables[0].Rows[0]["ShippingMethod"].ToString());
                }
                if (ds.Tables[0].Rows[0]["StoreID"] != null && ds.Tables[0].Rows[0]["StoreID"].ToString() != "")
                {
                    model.StoreID = int.Parse(ds.Tables[0].Rows[0]["StoreID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ShippingOn"] != null && ds.Tables[0].Rows[0]["ShippingOn"].ToString() != "")
                {
                    model.ShippingOn = DateTime.Parse(ds.Tables[0].Rows[0]["ShippingOn"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ShippingBy"] != null && ds.Tables[0].Rows[0]["ShippingBy"].ToString() != "")
                {
                    model.ShippingBy = ds.Tables[0].Rows[0]["ShippingBy"].ToString();
                }
                if (ds.Tables[0].Rows[0]["ImportOn"] != null && ds.Tables[0].Rows[0]["ImportOn"].ToString() != "")
                {
                    model.ImportOn = DateTime.Parse(ds.Tables[0].Rows[0]["ImportOn"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ImportBy"] != null && ds.Tables[0].Rows[0]["ImportBy"].ToString() != "")
                {
                    model.ImportBy = ds.Tables[0].Rows[0]["ImportBy"].ToString();
                }
                //if (ds.Tables[0].Rows[0]["LastUpdate"] != null && ds.Tables[0].Rows[0]["LastUpdate"].ToString() != "")
                {
                    //model.LastUpdate = DateTime.Parse(ds.Tables[0].Rows[0]["LastUpdate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["CustomerID"] != null && ds.Tables[0].Rows[0]["CustomerID"].ToString() != "")
                {
                    model.CustomerID = int.Parse(ds.Tables[0].Rows[0]["CustomerID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["PickUpAddressID"] != null && ds.Tables[0].Rows[0]["PickUpAddressID"].ToString() != "")
                {
                    model.PickUpAddressID = int.Parse(ds.Tables[0].Rows[0]["PickUpAddressID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ShipFromAddressID"] != null && ds.Tables[0].Rows[0]["ShipFromAddressID"].ToString() != "")
                {
                    model.ShipFromAddressID = int.Parse(ds.Tables[0].Rows[0]["ShipFromAddressID"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,OrderNumber,Status,OrderTime,Amount,SiteID,AddTime,Contact,Company,Street,City,Province,Country,CountryCode,Postcode,Phone,Email,TrackCode,ModifyOn,ShippingMethod,StoreID,ShippingOn,ShippingBy,ImportOn,ImportBy,LastUpdate,CustomerID,PickUpAddressID,ShipFromAddressID ");
            strSql.Append(" FROM Orders ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" ID,OrderNumber,Status,OrderTime,Amount,SiteID,AddTime,Contact,Company,Street,City,Province,Country,CountryCode,Postcode,Phone,Email,TrackCode,ModifyOn,ShippingMethod,StoreID,ShippingOn,ShippingBy,ImportOn,ImportBy,LastUpdate,CustomerID,PickUpAddressID,ShipFromAddressID ");
            strSql.Append(" FROM Orders ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }


        public DataSet GetUnCarrierOrders(int siteID)
        {
            string sql = @"SELECT Country,COUNT(*) as [count]
                              FROM orders
                              where [Status] in (1) and ShippingMethod is null and SiteID = " + siteID.ToString() + @"
                              group by Country";
            return DbHelperSQL.Query(sql);
        }

        public DataSet GetFulfilInfos(int siteID, int storeID, int carrierID,DateTime startDate, DateTime endDate)
        {
            string sql = @"SELECT ordernumber,TrackCode,shippingon
                              FROM orders
                              where [Status] in (" + ((int)Hermes.Model.OrderStatus.Shipped).ToString() + ") and ShippingMethod =" + carrierID.ToString()
                                                   + " and StoreID =" + storeID.ToString()
                                                   + " and SiteID = " + siteID.ToString()
                                                   + " and shippingon between '" + startDate.ToShortDateString() + "' and '" + endDate.ToShortDateString() + "'";
            return DbHelperSQL.Query(sql);
        }


        public DataSet UnSyncOrders(int carrierID)
        {
            string sql = @"SELECT OrderNumber,SiteID,ID
                              FROM orders
                              where [Status] in (1) and ShippingMethod = " + carrierID + " and TrackCode is null ";
            return DbHelperSQL.Query(sql);
        }

        public DataSet UnLabelOrders(int carrierID)
        {
            string sql = @"SELECT OrderNumber,SiteID,ID,TrackCode
                              FROM orders
                              where [Status] in (1) and ShippingMethod = " + carrierID + " and TrackCode is not null ";
            return DbHelperSQL.Query(sql);
        }

        public int UpdateTrackingNo(string orderNumber, int siteID, long orderID, string trackingNo)
        {
            string sql = @"update orders set TrackCode = '" + trackingNo + @"'
                             
                              where [Status] in (1) and ordernumber ='" + orderNumber + "' and SiteID = " + siteID.ToString() + "and  id= " + orderID.ToString();

            return DbHelperSQL.ExecuteSql(sql);
        }

        public int AssignCarrier(int carrierID, int siteID, string[] countries)
        {
            string countryWhere = " Country in (";
            foreach (string country in countries)
            {
                countryWhere += "'" + country + "', ";
            }

            countryWhere += " '')";

            string sql = @"update orders set ShippingMethod = " + carrierID + @"
                              
                              where [Status] in (1) and ShippingMethod is null and SiteID = " + siteID.ToString() + "and  " + countryWhere;

            return DbHelperSQL.ExecuteSql(sql);
        }

        public int UpdateOrdersStatus(int[] orderid, Hermes.Model.OrderStatus status)
        {
            string idWhere = " id in (";
            foreach (int id in orderid)
            {
                idWhere += "" + id + ", ";
            }

            idWhere += " -1)";

            string sql = @"update orders set Status = " + ((int)(status)).ToString() + @"
                              where [Status] in (1) and  " + idWhere;

            return DbHelperSQL.ExecuteSql(sql);
        }

        public int ShipOrder(long orderID, string shippingUser)
        {
            string idWhere = " id = " + orderID.ToString();


            string sql = @"update orders set Status = " + ((int)(Hermes.Model.OrderStatus.Shipped)).ToString() + @"
                              ,shippingon = '" + DateTime.UtcNow.ToString() + @"'
                              ,shippingby='" + shippingUser + @"'
                              where   " + idWhere;

            return DbHelperSQL.ExecuteSql(sql);
        }

        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@IsReCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = "Orders";
            parameters[1].Value = "ID";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/

        #endregion  Method
    }
}

